package com.example.androidlearning2200;

import androidx.appcompat.app.AppCompatActivity;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;

import com.example.androidlearning2200.utils.SQLUtil;

public class SQLiteActivity extends AppCompatActivity {

    private SQLiteDatabase sd;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite);
        findViewById(R.id.btnOpenDB).setOnClickListener(view -> openOrCreateDB());
        findViewById(R.id.btnCreateTable).setOnClickListener(view -> createTable());
        findViewById(R.id.btnInsertData).setOnClickListener(view -> insertData());
        findViewById(R.id.btnQueryData).setOnClickListener(view -> queryData());
        findViewById(R.id.btnUpdateData).setOnClickListener(view -> updateData());
        findViewById(R.id.btnDeleteData).setOnClickListener(view -> deleteData());
        findViewById(R.id.btnDeleteTable).setOnClickListener(view -> dropTable());
    }

    /**
     * 删除数据表
     */
    private void dropTable() {
        // sql语句
        String sqlDrop = "drop table table_stu_info";
        // 执行sql语句
        sd.execSQL(sqlDrop);
    }

    /**
     * 删除数据
     */
    private void deleteData() {
        // 删除数据的sql语句
        String sqlDelete = "delete from table_stu_info where _id = 3";
        // 执行sql语句
        sd.execSQL(sqlDelete);
    }

    /**
     * 更新数据
     */
    private void updateData() {
        // 更新数据的sql语句
        String sqlUpdate = "update table_stu_info set stu_name = '李金杭' where _id = 2";
        // 执行sql语句
        sd.execSQL(sqlUpdate);
    }

    /**
     * 查询数据
     */
    private void queryData() {
        Cursor cursor = sd.query("table_stu_info", null, null, null, null, null, null);
        while (cursor.moveToNext()) {
            int id = cursor.getInt(0);
            String name = cursor.getString(1);
            String code = cursor.getString(2);
            Log.d("SQLite", id + name + code);
        }
        cursor.close();
    }

    /**
     * 插入数据
     */
    private void insertData() {
        // 插入数据的sql语句
        String sqlInsertData = "insert into table_stu_info(stu_name,stu_code) values('陈乐平','202203010044')";
        // 执行sql语句
        sd.execSQL(sqlInsertData);
    }

    /**
     * 创建数据表
     */
    private void createTable() {
        // 如果表不存在，则创建表
        if (SQLUtil.doesTableExist(sd, "table_stu_info") == false) {
            // 建表的sql语句
            String sqlCreateTable = "create table table_stu_info(_id integer primary key autoincrement,stu_name text,stu_code text)";
            // 执行sql语句
            sd.execSQL(sqlCreateTable);
        }
    }

    /**
     * 打开或创建数据库
     */
    private void openOrCreateDB() {
        sd = SQLiteDatabase.openOrCreateDatabase("/data/data/com.example.androidlearning2200/student.db", null);
    }
}